package com.bradz.mytv;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import android.content.Context;
import android.database.sqlite.SQLiteCursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class classChannelDatabase extends SQLiteOpenHelper{

    private static final String DATABASE_NAME = "myTVDatabase";
    
    private static final int DATABASE_VERSION = 11;
    private static final String CHANNEL_TABLE_NAME = "tblChannels";
    private static final String CHANNEL_TABLE_CREATE =
            "CREATE TABLE " + CHANNEL_TABLE_NAME + " ( _id Integer PRIMARY KEY AUTOINCREMENT, Name TEXT);";

    private static final String PATH_TABLE_NAME = "tblPaths";
	private static final String PATH_TABLE_CREATE =
            "CREATE TABLE " + PATH_TABLE_NAME + " ( _id Integer PRIMARY KEY AUTOINCREMENT, Channelid Integer, Channelpath Text);";
	
	private static final String SETTINGS_TABLE_NAME = "tblSettings";
	private static final String SETTINGS_TABLE_CREATE =
            "CREATE TABLE " + SETTINGS_TABLE_NAME + " (RealTime boolean, Channelscheduling boolean, Commercials boolean, Commercialpath Text, Lastchannel Integer, Lastepisode Integer, Errors boolean);";
	
	//Remember that this table has to be wiped whenever any of the channel settings are changed
	private static final String RECOVERY_TABLE_NAME = "tblChannelRecovery";
	private static final String RECOVERY_TABLE_CREATE =
            "CREATE TABLE " + RECOVERY_TABLE_NAME + " (Channelid Integer, Channelepisode Integer, Episodeoffset SIGNED BIGINT, Episodelength SIGNED BIGINT);";
	
	//Remember that this table has to be wiped whenever any of the channel settings are changed
	private static final String COMMERCIAL_TABLE_NAME = "tblCommercialHolder";
	private static final String COMMERCIAL_TABLE_CREATE =
	      "CREATE TABLE " + COMMERCIAL_TABLE_NAME + " (_id Integer PRIMARY KEY AUTOINCREMENT, CommercialUrl Text, Genre Text, Duration Integer);";

	classChannelDatabase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(CHANNEL_TABLE_CREATE);
    db.execSQL(PATH_TABLE_CREATE);
    db.execSQL(SETTINGS_TABLE_CREATE);
    db.execSQL(RECOVERY_TABLE_CREATE);
    db.execSQL(COMMERCIAL_TABLE_CREATE);
    db.execSQL("INSERT INTO "+ SETTINGS_TABLE_NAME + " ( RealTime,Channelscheduling,Commercials,Commercialpath, Lastchannel, Lastepisode, Errors)"+" VALUES (0,0,0,'',0,0,0);");
}

public void perform_writable_task(String[] task){
	SQLiteDatabase db=this.getWritableDatabase();
	try{
		for (String query: task){
			db.execSQL(query);
		}
		db.close();
	}catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
}

//Can't return cursor after the db is closed. should find workaround
/*public SQLiteCursor perform_readable_task(String task){
	SQLiteDatabase db=this.getReadableDatabase();
	SQLiteCursor c = null;
	try{
		c = (SQLiteCursor) db.rawQuery(task,new String [] {});
		db.close();
		return c;
	}catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
		return c;
	}
}*/

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      // TODO Auto-generated method stub
      
      db.execSQL("DROP TABLE IF EXISTS "+CHANNEL_TABLE_NAME);
      db.execSQL("DROP TABLE IF EXISTS "+PATH_TABLE_NAME);
      db.execSQL("DROP TABLE IF EXISTS "+SETTINGS_TABLE_NAME);
      db.execSQL("DROP TABLE IF EXISTS "+RECOVERY_TABLE_NAME);
      db.execSQL("DROP TABLE IF EXISTS "+COMMERCIAL_TABLE_NAME);
      onCreate(db);
}

public void reset_settings(){
	SQLiteDatabase db=this.getWritableDatabase();
	db.execSQL("DROP TABLE IF EXISTS "+SETTINGS_TABLE_NAME);
	db.execSQL(SETTINGS_TABLE_CREATE);
	db.execSQL("INSERT INTO "+ SETTINGS_TABLE_NAME + " ( RealTime,Channelscheduling,Commercials,Commercialpath, Lastchannel, Lastepisode, Errors)"+" VALUES (0,0,0,'',0,0,0);");
	db.close();
}

public void add_commercial(String url, String genre, int duration){
	Log.i("classChannelDatabase - add_commercial", "url: " + url + " genre: " + genre + " duration: " + duration);
	if(!commercial_in_database(url)){
		String[] query =  {"INSERT INTO "+ COMMERCIAL_TABLE_NAME + " ( CommercialUrl, Genre, Duration )"+" VALUES ('"+url+"','"+genre+"',"+duration+");"};
		perform_writable_task(query);
	}
}

public void clear_commercials(){
	Log.i("classChannelDatabase - clear_commercials", "clearing...");
	String[] query =  {"DROP TABLE IF EXISTS "+COMMERCIAL_TABLE_NAME,COMMERCIAL_TABLE_CREATE};
	perform_writable_task(query);
}

public void reset_recovery_data(){
	Log.i("classChannelDatabase - reset_recovery_data", "clearing...");
	String[] query =  {"DROP TABLE IF EXISTS "+RECOVERY_TABLE_NAME,RECOVERY_TABLE_CREATE};
	perform_writable_task(query);
}

public void update_settings(classSettingsHolder settings){
	String[] query =  {"UPDATE "+ SETTINGS_TABLE_NAME + " SET RealTime = " + (settings.real_time?1:0) + ", Channelscheduling = " + (settings.channel_scheduling?1:0) + ", Commercials = " +
			(settings.commercials?1:0) + ", Errors = " + (settings.show_errors?1:0) +";"};
	perform_writable_task(query);
}

public void set_last_channel_episode(int[] information){
	String[] query =  {"UPDATE "+ SETTINGS_TABLE_NAME + " SET Lastchannel = " + information[0] + ", Lastepisode = " + information[1] + ";"};
	perform_writable_task(query);
}

public void add_channel(String channel_name){
	if(!return_used_channel(channel_name)){
		String[] query =  {"INSERT INTO "+ CHANNEL_TABLE_NAME + " ( Name )"+" VALUES ('"+channel_name+"');"};
		perform_writable_task(query);
	}
}

public void add_channel_episodes(int channel_id, String episode_path){
	Log.i("channeldatabase - add_channel_episodes", "Channel: " + channel_id + " path: " + episode_path);
	if(return_used_channel(channel_id)){
		String[] query =  {"INSERT INTO "+ PATH_TABLE_NAME + " ( Channelid, Channelpath )"+" VALUES ("+channel_id+",'" + episode_path +"');"};
		perform_writable_task(query);
	}
}

public void delete_channel(int channel_id){
	if(return_used_channel(channel_id)){
		SQLiteDatabase db=this.getWritableDatabase();
		try{
			db.delete(PATH_TABLE_NAME, "Channelid=" + channel_id, null);
			db.delete(CHANNEL_TABLE_NAME, "_id=" + channel_id, null);
			db.close();
		}catch(Exception e){
			db.close();
		}
	}
}

public void delete_single_channel_episode(int channel_id, String episode_path){
	if(return_used_channel(channel_id)){
		SQLiteDatabase db=this.getWritableDatabase();
		try{
			db.delete(PATH_TABLE_NAME, "Channelid=" + channel_id + " and Channelpath = '" + episode_path + "'", null);
			db.close();
		}catch(Exception e){
			db.close();
		}
	}
}

public void delete_channel_episodes(int channel_id){
	if(return_used_channel(channel_id)){
		SQLiteDatabase db=this.getWritableDatabase();
		try{
			db.delete(PATH_TABLE_NAME, "Channelid=" + channel_id, null);
			db.close();
		}catch(Exception e){
			db.close();
		}
	}
}

//Channelid Integer, Channelepisode Integer, Episodeoffset Text
public void set_recovery_table(ArrayList<classRecovery> recovery_list){
	for (classRecovery recovery_item: recovery_list){
		try{
			if(return_used_recovery_table_path(recovery_item.channel_number)){
				String[] query =  {"UPDATE "+ RECOVERY_TABLE_NAME + " SET Episodeoffset = " + recovery_item.episode_offset + ", Channelepisode = " + recovery_item.channel_episode + ", Episodelength = " + recovery_item.episode_length + " where Channelid = " + recovery_item.channel_number + ";"};
				perform_writable_task(query);
			}else{
				String[] query =  {"INSERT INTO "+ RECOVERY_TABLE_NAME + " ( Episodelength,Episodeoffset,Channelepisode,Channelid )"+" VALUES ("+recovery_item.episode_length+","+recovery_item.episode_offset+","+recovery_item.channel_episode+","+recovery_item.channel_number+");"};
				perform_writable_task(query);
			}
		}catch(Exception e){
			Log.v("return_used error: ", ""+e);
		}
	}
}

public int[] get_last_channel_episode(){
	int[] return_values = new int[2];
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT Lastchannel, Lastepisode FROM "+ SETTINGS_TABLE_NAME + " ;",new String [] {});
		c.moveToFirst();
		return_values[0] = c.getInt(c.getColumnIndexOrThrow("Lastchannel"));
		return_values[1] = c.getInt(c.getColumnIndexOrThrow("Lastepisode"));
		db.close();
	}catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
	return return_values;
}

public void print_all_commercials_in_database(){
	Log.i("classChannelDatabase - get_all_commercials_in_database", "printing...");
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT * from " + COMMERCIAL_TABLE_NAME + ";",new String [] {});
		for (int i = 0; i < c.getCount(); i++){
	    	c.moveToPosition(i);
	    	Log.i("classChannelDatabase - print_all_commercials_in_database","Ch path: " + c.getString(c.getColumnIndexOrThrow("CommercialUrl")));
		}
		db.close();
    }catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
}

public ArrayList <String> get_all_commercials_in_database(){
	Log.i("classChannelDatabase - get_all_commercials_in_database", "");
	ArrayList <String> return_value = new ArrayList <String>();
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT * from " + COMMERCIAL_TABLE_NAME + ";",new String [] {});
		for (int i = 0; i < c.getCount(); i++){
	    	c.moveToPosition(i);
	    	return_value.add(c.getString(c.getColumnIndexOrThrow("CommercialUrl")));
	    }
		db.close();
    }catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
	return return_value;
}

public boolean commercial_in_database(String url){
	boolean return_value = true;
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT * from " + COMMERCIAL_TABLE_NAME + " WHERE CommercialUrl = '" + url + "';",new String [] {});
		return_value = c.getCount()>0?true:false;
		db.close();
    }catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
	return return_value;
}

public ArrayList<classRecovery> get_recovery_table(){
	ArrayList<classRecovery> return_list = new ArrayList<classRecovery>();
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT * from " + RECOVERY_TABLE_NAME + ";",new String [] {});
		for(int i = 0; i < c.getCount(); i++){
	    	c.moveToPosition(i);
	    	return_list.add(new classRecovery(c.getInt(c.getColumnIndexOrThrow("Channelid")), 
	    			c.getInt(c.getColumnIndexOrThrow("Channelepisode")),
	    			c.getLong(c.getColumnIndexOrThrow("Episodeoffset")),
	    			c.getLong(c.getColumnIndexOrThrow("Episodelength"))));
	    }
		db.close();
	}catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
	return return_list;
}

public boolean return_used_recovery_table_path(int channel_id){
	boolean return_value = true;
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT * from " + RECOVERY_TABLE_NAME + " WHERE Channelid = " + channel_id + ";",new String [] {});
		return_value = c.getCount()>0?true:false;
		db.close();
	}catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
	return return_value;
}

public classSettingsHolder get_settings(){
	classSettingsHolder settings = new classSettingsHolder(false, false, false, "", false);
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT * FROM "+ SETTINGS_TABLE_NAME + " ;",new String [] {});
		c.moveToFirst();
		settings = new classSettingsHolder(c.getInt(c.getColumnIndexOrThrow("RealTime"))>0,c.getInt(c.getColumnIndexOrThrow("Channelscheduling"))>0,
				c.getInt(c.getColumnIndexOrThrow("Commercials"))>0,"",c.getInt(c.getColumnIndexOrThrow("Errors"))>0);
		db.close();
	}catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
	return settings;
}

public boolean return_used_channel(String channel_name){
	boolean return_value = true;
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT * from " + CHANNEL_TABLE_NAME + " WHERE Name = '" + channel_name + "';",new String [] {});
		return_value = c.getCount()>0?true:false;
		db.close();
	}catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
	return return_value;
}

public boolean return_used_channel(int channel_id){
	boolean return_value = true;
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT * from " + CHANNEL_TABLE_NAME + " WHERE _id = " + channel_id + ";",new String [] {});
		return_value = c.getCount()>0?true:false;
		db.close();
	}catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
	return return_value;
}

public boolean return_used_episode_path(int channel_id, String episode_path){
	boolean return_value = true;
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT * from " + PATH_TABLE_NAME + " WHERE Channelid = " + channel_id + " and Channelpath = '" + episode_path + "';",new String [] {});
		return_value = c.getCount()>0?true:false;
		db.close();
	}catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
	return return_value;
}

public int return_episode_path_size(){
	int return_value = 0;
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT * from " + PATH_TABLE_NAME + ";",new String [] {});
		return_value = c.getCount();
		db.close();
	}catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
	return return_value;
}

public String get_channel_name(int channel_id){
	String return_value = "";
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT * from " + CHANNEL_TABLE_NAME + " WHERE _id = " + channel_id + ";",new String [] {});
		c.moveToFirst();
	    return_value = c.getCount()>0?c.getString(c.getColumnIndexOrThrow("Name")):"";
	    db.close();
	}catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
    return return_value;
}

public int get_channel_id(String channel_name){
	int return_value = -1;
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT * from " + CHANNEL_TABLE_NAME + " WHERE Name = '" + channel_name + "';",new String [] {});
		c.moveToFirst();
	    return_value = c.getCount()>0?c.getInt(c.getColumnIndexOrThrow("_id")):-1;
	    db.close();
	}catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
    return return_value;
}

public ArrayList<String> get_channel_paths(int channel_id){
	ArrayList<String> return_value = new ArrayList<String>();
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT * from " + PATH_TABLE_NAME + " WHERE Channelid = " + channel_id + ";",new String [] {});
		for(int i = 0; i < c.getCount(); i++){
	    	c.moveToPosition(i);
	    	Log.i("classChannelDatabase - get_channel_paths", "Ch path: " + c.getString(c.getColumnIndexOrThrow("Channelpath")));
	    	return_value.add(c.getString(c.getColumnIndexOrThrow("Channelpath")));
	    }
		db.close();
	}catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
	return return_value;
}

public ArrayList<Integer> get_all_channels(){
	ArrayList<Integer> return_value = new ArrayList<Integer>();
	SQLiteDatabase db=this.getReadableDatabase();
	try{
		SQLiteCursor c = (SQLiteCursor) db.rawQuery("SELECT * from " + CHANNEL_TABLE_NAME + ";",new String [] {});
		for(int i = 0; i < c.getCount(); i++){
	    	c.moveToPosition(i);
	    	return_value.add(c.getInt(c.getColumnIndexOrThrow("_id")));
	    }
		db.close();
	}catch(Exception e){
		Log.v("return_used error: ", ""+e);
		db.close();
	}
	return return_value;
}

}
